/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package xml.dao;

import java.sql.*;
import xml.entity.Employer;
import xml.entity.Job;
import xml.entity.Jobs;
import xml.util.DBConnection;

/**
 *
 * @author Kyros
 */
public class JobDAO {

    DBConnection conInfo;

    public Jobs getEmployerAllJob(String username) throws SQLException, Exception {

        ResultSet rs = null;
        Connection conn = null;
        Statement stm = null;
        String strQuery;
        EmployerDAO employerDao = new EmployerDAO();
        Jobs jobList = new Jobs();
        int employerId = employerDao.getEmployerIdByUsername(username);
        conInfo = new DBConnection();
        Job job;

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            strQuery = "SELECT * FROM job WHERE employerId = '" + employerId + "'";

            stm = conn.createStatement();
            rs = stm.executeQuery(strQuery);
            while (rs.next()) {
                job = new Job();
                job.setCareer(rs.getString("career"));
                job.setCity(rs.getString("city"));
                job.setDayOfProfileDealine(rs.getInt("dayOfProfileDealine"));
                job.setEmployeeId(rs.getInt("employerId"));
                job.setEmployerId(employerId);
                job.setJobBenefit(rs.getString("jobBenefit"));
                job.setJobDesc(rs.getString("jobDesc"));
                job.setJobId(rs.getInt("jobId"));
                job.setJobType(rs.getShort("jobType"));
                job.setJobLimit(rs.getInt("jobLimit"));
                job.setJobName(rs.getString("jobName"));
                job.setMonthOfProfileDealine(rs.getInt("monthOfProfileDealine"));
                job.setNeededPos(rs.getString("neededPos"));
                job.setOtherRequire(rs.getString("otherRequire"));
                job.setProfileRequire(rs.getString("profileRequire"));
                job.setRequiredEdu(rs.getString("requiredEdu"));
                job.setRequiredExp(rs.getString("requiredExp"));
                job.setSalaryOffer(rs.getString("salaryOffer"));
                job.setYearOfProfileDeadline(rs.getInt("yearOfProfileDeadline"));
                jobList.getJob().add(job);
            }
            return jobList;
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

        public Jobs getAllJobs() throws SQLException, Exception {

        ResultSet rs = null;
        Connection conn = null;
        Statement stm = null;
        String strQuery;
        Jobs jobList = new Jobs();
        conInfo = new DBConnection();
        Job job;

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            strQuery = "SELECT * FROM job";

            stm = conn.createStatement();
            rs = stm.executeQuery(strQuery);
            while (rs.next()) {
                job = new Job();
                job.setCareer(rs.getString("career"));
                job.setCity(rs.getString("city"));
                job.setDayOfProfileDealine(rs.getInt("dayOfProfileDealine"));
                job.setEmployeeId(rs.getInt("employerId"));
                job.setEmployerId(rs.getInt("employerId"));
                job.setJobBenefit(rs.getString("jobBenefit"));
                job.setJobDesc(rs.getString("jobDesc"));
                job.setJobId(rs.getInt("jobId"));
                job.setJobType(rs.getShort("jobType"));
                job.setJobLimit(rs.getInt("jobLimit"));
                job.setJobName(rs.getString("jobName"));
                job.setMonthOfProfileDealine(rs.getInt("monthOfProfileDealine"));
                job.setNeededPos(rs.getString("neededPos"));
                job.setOtherRequire(rs.getString("otherRequire"));
                job.setProfileRequire(rs.getString("profileRequire"));
                job.setRequiredEdu(rs.getString("requiredEdu"));
                job.setRequiredExp(rs.getString("requiredExp"));
                job.setSalaryOffer(rs.getString("salaryOffer"));
                job.setYearOfProfileDeadline(rs.getInt("yearOfProfileDeadline"));
                jobList.getJob().add(job);
            }
            return jobList;
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public int getNextJobId() throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();
        ResultSet rs = null;
        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            String strQuery = "SELECT jobId FROM job";

            stm = conn.createStatement();
            rs = stm.executeQuery(strQuery);

            int result = 0;
            int i = 0;
            while (rs.next()) {
                result = Integer.parseInt(rs.getString("jobId"));
                i = i++;
            }

            stm.close();
            conn.close();

            return result + 1;

        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public void insertJob(Job job) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            StringBuilder strBuilder = new StringBuilder();

            strBuilder.append("INSERT INTO job (jobId, employerId, employeeId, jobName, jobDesc, "
                    + "career, jobType, neededPos, city, jobLimit, salaryOffer, jobBenefit, requiredExp, requiredEdu, "
                    + "otherRequire, dayOfProfileDealine, monthOfProfileDealine, yearOfProfileDeadline, profileRequire)"
                    + " VALUES ('").append(job.getJobId()).
                    append("', '").append(job.getEmployerId()).
                    append("', '").append(job.getEmployeeId()).
                    append("', '").append(job.getJobName()).
                    append("', '").append(job.getJobDesc()).
                    append("', '").append(job.getCareer()).
                    append("', '").append(job.getJobType()).
                    append("', '").append(job.getNeededPos()).
                    append("', '").append(job.getCity()).
                    append("', '").append(job.getJobLimit()).append("', '").
                    append(job.getSalaryOffer()).append("', '").
                    append(job.getJobBenefit()).append("', '").
                    append(job.getRequiredExp()).append("', '").
                    append(job.getRequiredEdu()).append("', '").
                    append(job.getOtherRequire()).append("', '").
                    append(job.getDayOfProfileDealine()).append("', '").
                    append(job.getMonthOfProfileDealine()).append("', '").
                    append(job.getYearOfProfileDeadline()).append("', '").
                    append(job.getProfileRequire()).append("')");
//            System.out.println("sad"+strBuilder.toString());
            stm = conn.createStatement();
            stm.executeUpdate(strBuilder.toString());

            stm.close();
            conn.close();
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public void updateJob(Job job, int jobId) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            StringBuilder strBuilder = new StringBuilder();

            strBuilder.append("UPDATE Job SET employerId = '").append(job.getEmployerId()).append("', ");
            strBuilder.append("employeeId = '").append(job.getEmployeeId()).append("', ");
            strBuilder.append("jobName = '").append(job.getJobName()).append("', ");
            strBuilder.append("jobDesc = '").append(job.getJobDesc()).append("', ");
            strBuilder.append("career = '").append(job.getCareer()).append("', ");
            strBuilder.append("jobType = '").append(job.getJobType()).append("', ");
            strBuilder.append("neededPos = '").append(job.getNeededPos()).append("', ");
            strBuilder.append("city = '").append(job.getCity()).append("', ");
            strBuilder.append("jobLimit = '").append(job.getJobLimit()).append("', ");
            strBuilder.append("salaryOffer = '").append(job.getSalaryOffer()).append("', ");
            strBuilder.append("jobBenefit = '").append(job.getJobBenefit()).append("', ");
            strBuilder.append("requiredExp = '").append(job.getRequiredExp()).append("', ");
            strBuilder.append("requiredEdu = '").append(job.getRequiredEdu()).append("', ");
            strBuilder.append("otherRequire = '").append(job.getOtherRequire()).append("', ");
            strBuilder.append("dayOfProfileDealine = '").append(job.getDayOfProfileDealine()).append("', ");
            strBuilder.append("monthOfProfileDealine = '").append(job.getMonthOfProfileDealine()).append("', ");
            strBuilder.append("yearOfProfileDeadline = '").append(job.getYearOfProfileDeadline()).append("', ");
            strBuilder.append("profileRequire = '").append(job.getProfileRequire()).append("' ");
            strBuilder.append(" WHERE jobId = '").append(jobId).append("'");


//            System.out.println("update job query:" + strBuilder);
            stm = conn.createStatement();
            stm.executeUpdate(strBuilder.toString());

            stm.close();
            conn.close();
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public void deleteJob(int jobId) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();
        ResultSet rs = null;
        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            String strQuery = "DELETE FROM job WHERE jobId = '"+jobId+"'";
//            System.out.println("sad"+strQuery);
            stm = conn.createStatement();
            stm.executeUpdate(strQuery);
            stm.close();
            conn.close();

        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }
}
